package com.haisen.poi.basic_poi;

import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.BigDecimalConverter;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.xml.sax.SAXException;

import java.io.*;
import java.math.BigDecimal;
import java.util.*;


@Slf4j
public class BigExcel2TextUtil extends BigExcelReader{


	   private File outfile = null;
	   File file = null;
	   FileWriter fw =  null;
	   BufferedWriter bw = null;

        private StringBuilder appendLine = new StringBuilder();
		private static BigExcel2TextUtil bExcelUtil= new BigExcel2TextUtil();
		private Map<Integer,String> tableFields = new HashMap<>();

		public Map<Integer, String> getTableFields() {
			return tableFields;
		}

		private BigExcel2TextUtil() {
		}


		public static BigExcel2TextUtil getInstance(){
			//注册字符串，数字（1900-1-1的天数）到日期转换器
			ConvertUtils.register(new SQLDateConverter(),Date.class);
			//注册bigdecimalConvert转换器
			BigDecimalConverter bd = new BigDecimalConverter(BigDecimal.ZERO);
			ConvertUtils.register(bd, BigDecimal.class);
			return bExcelUtil ;
		}

	public void readExcel2Txt(String sourceFile,String destFile){
		try {
			 //清空上次的字段映射
			  tableFields.clear();
			  outfile = new File(destFile);
			  fw = new FileWriter(outfile);
			  bw = new BufferedWriter(fw);
			//1、读取excel源，生成TXT数据
			excelReaderByPath(sourceFile);
			//解析文档
			parse();

		} catch (IOException | OpenXML4JException | SAXException e) {
			e.printStackTrace();
		} finally {
			try {
				bw.close();
				fw.close();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}
	}



	@Override
	protected void outputRow(String[] datas, String[] rowTypes, int rowIndex) {
		      //获取字段名称
		      if (rowIndex == 0) InitDDL(datas, rowTypes, rowIndex);
		      else {
		      	//获取字段类型
				  if (rowIndex == 1) InitDDL(datas, rowTypes, rowIndex);
				  for(int colIndex = 0; colIndex < datas.length; colIndex ++){
				  	  //字段全为空，不导入
				  	  if(tableFields.get(colIndex) == null) continue;
					  appendLine.append(datas[colIndex]==null ? "" : datas[colIndex]);
					  appendLine.append("|");
				  }
			  }
		//将解析写入到表格
		try {
			 if (appendLine.length() > 1){
				 bw.write(appendLine.deleteCharAt(appendLine.length()-1).toString());
				 bw.flush();
				 bw.newLine();
				 bw.flush();
				 appendLine.setLength(0);
			 }
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 初始化表格元数据，字段，字段名
	 * @param datas
	 * @param rowTypes
	 * @param rowIndex
	 */
	//如果第二行为空行，则字段对应不上
   void InitDDL(String[] datas, String[] rowTypes, int rowIndex){

	   for (int colIndex = 0; colIndex < datas.length; colIndex ++){
	   	   if (rowIndex == 0){
			   tableFields.isEmpty();
			   tableFields.put(colIndex,datas[colIndex]);
			   //log.info("第{}行，value={}",colIndex,datas[colIndex]);
		   }
	   	   else {
	   	   	      //第二行为null 或者值为null，不导入处理
			     if (rowTypes[colIndex] != null && !"null".equals(rowTypes[colIndex])){
			     	  //excel中手机号码识别为数字了，导入到hive为NULL，此时类型改为String
			     	 if ("INT".equals(rowTypes[colIndex]) && datas[colIndex].length() > 10){
						 tableFields.put(colIndex,tableFields.get(colIndex)+":"+"STRING" );
					 } else
			     	tableFields.put(colIndex,tableFields.get(colIndex)+":"+rowTypes[colIndex] );
				 } else {
			     	  //为空不导入算了
					 tableFields.remove(colIndex);
				 }
			   //log.info("第{}行，value={}",colIndex,datas[colIndex]);
		   }
	   }
	   //log.info("{}",JSON.toJSONString(tableFields));
   }

   /*public String createTableSql(String tableName){
   //	log.info("fields={}", JSON.toJSONString(tableFields));
   	  StringBuilder sb = new StringBuilder();
   	  sb.append("create table "+tableName+"(\n");
   	  for(Map.Entry<Integer,String> entry : tableFields.entrySet()){
   	  	String[] fieldNameAndType = entry.getValue().split(":");
   	      sb.append(fieldNameAndType[0]+" "+ fieldNameAndType[1]+",comment '"+fieldNameAndType[0]+"'\n");
		  sb.append(fieldNameAndType[0]+",\n");
	  }	//删除的多余的一个,
	   if (sb.length() >1 )
	   	sb.deleteCharAt(sb.length()-2);
	  sb.append(")\n");
	  sb.append("ROW FORMAT DELIMITED\n");
	  sb.append("FIELDS TERMINATED BY '|'\n");
	  sb.append("COLLECTION ITEMS TERMINATED BY '-'\n");
	  sb.append("MAP KEYS TERMINATED BY ':'\n");
	  sb.append("LINES TERMINATED BY '\\n';");
	  return sb.toString();
   }*/

}
